--
-- 棋牌游戏sql脚本(postgresql)
-- huangtao117@gmail.com
--
-- 1.安装好postgresql后建立demo数据库
-- createdb demo
-- 2.命令行管理工具连接demo
-- psql demo
-- 3.导入脚本
-- \i demo_postgresql.sql
-- 4.导入成功后可以查看表和函数
-- \df或者\dt
-- 5.退出
-- \q
--

--
-- 表
--

-- 账号表
CREATE TABLE IF NOT EXISTS t_account(
    account text PRIMARY KEY,       -- 账号
    password text,                  -- 密码
    temppwd text,                   -- 临时密码
    type text                       -- 帐号类型(默认,游客,微信,QQ,新浪微博...)
);

-- 平台用户表
CREATE TABLE IF NOT EXISTS t_user(
    userid int PRIMARY KEY,         -- 游戏平台数字账号
    account text,                   -- 账号
    name text,                      -- 名字
    sex smallint DEFAULT 0,         -- 性别(0女;1男)
    head_img text,                  -- 头像
    paypoint bigint DEFAULT 0,      -- 支付点(虚拟币)(一般等值于货币)
    play_exp bigint DEFAULT 0,      -- 经验值
    gold bigint DEFAULT 0,          -- 财富(银子,金币等)
    box_gold bigint DEFAULT 0,      -- 保险箱财富
    diamond int DEFAULT 0,          -- 房卡,钻石
    score bigint DEFAULT 0,         -- 积分
    total_games bigint DEFAULT 0,   -- 游戏对局数
    realname text,                  -- 真实姓名
    identitycard text,              -- 身份证
    telphone text,                  -- 手机号码
    address text,                   -- 快递地址
    flag_forbid int DEFAULT 0,      -- 是否冻结
    flag_admin int DEFAULT 0,       -- 是否管理员
    vip_level int DEFAULT 0,        -- vip等级
    group_id int DEFAULT 0,         -- 社团(团队,战队)ID
    ip_reg text,                    -- 注册IP
    ip_login text,                  -- 最后登录IP
    dev_reg text,                   -- 注册设备(如pc的机器码)
    dev_login text,                 -- 登录设备(如pc的机器码)
    dev_bind text,                  -- 绑定设备
    time_reg timestamp,             -- 注册时间戳
    time_login timestamp            -- 最后登录时间戳
);
-- 普通用户起始号,保留1-999为vip用户号
CREATE SEQUENCE userid_sequence START 1000;

-- 保留关键词
CREATE TABLE IF NOT EXISTS t_reserve(
    id serial PRIMARY KEY,      -- 编号
    rsv text                    -- 文本
);
insert into t_reserve(rsv) values('.');
insert into t_reserve(rsv) values('_');
insert into t_reserve(rsv) values('GM');
insert into t_reserve(rsv) values('GUEST');
insert into t_reserve(rsv) values('QQ');

-- 禁止ip地址表
CREATE TABLE IF NOT EXISTS t_forbidip(
    id serial PRIMARY KEY,      -- 编号
    ip text                     -- ip地址192.168.0.*
);

-- 禁止设备表
CREATE TABLE IF NOT EXISTS t_forbiddev(
    id serial PRIMARY KEY,      -- 编号
    device text,                -- 设备标示(网卡号、手机卡序列号)
    devtype text                -- 设备类型
);

-- 房间表
CREATE TABLE IF NOT EXISTS t_room(
    uuid text NOT NULL,         -- 房间UUID
    id text NOT NULL,           -- 房号
    set_info text,              -- 房间配置信息
    time_create timestamp,      -- 创建时间
    user_id1 int DEFAULT 0,
    user_name1 text,
    user_id2 int DEFAULT 0,
    user_name2 text,
    user_id3 int DEFAULT 0,
    user_name3 text,
    user_id4 int DEFAULT 0,
    user_name4 text,
    PRIMARY KEY(uuid),
    UNIQUE(id)
);

-- 登录日志表
CREATE TABLE IF NOT EXISTS log_login(
    id serial PRIMARY KEY,      -- 编号
    userid int,                -- 用户ID
    ip_login text,              -- 登录IP
    app_login text,             -- 登录应用
    dev_login text,             -- 登录设备
    time_login timestamp        -- 登录时间
);

-- 兑换日志表
CREATE TABLE IF NOT EXISTS log_buy(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    buyid int,                  -- 购买物品ID
    paypoint bigint,            -- 使用支付点
    point_new bigint,           -- 原有支付点
    point_old bigint,           -- 新支付点
    score_new bigint,           -- 原财富积分
    score_old bigint,           -- 新财富积分
    time_buy timestamp
);

-- 记分日志表
CREATE TABLE IF NOT EXISTS log_score(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    old_score bigint,           -- 原积分
    new_score bigint,           -- 新积分
    pay int DEFAULT 0,          -- 服务费
    vs_info text,               -- 对局信息
    time_score timestamp        -- 记分时间
);

-- 充值日志
CREATE TABLE IF NOT EXISTS log_pay(
    id serial PRIMARY KEY,      -- 编号
    userid text,                -- 用户ID
    pay_mode text,              -- 支付途径
    pay_tid text,               -- 交易号(卡号)
    pay_money bigint,           -- 充值人民币
    point_new bigint,           -- 新支付点
    point_old bigint,           -- 旧支付点
    score_new bigint,           -- 新财富点
    score_old bigint,           -- 旧财富点
    time_pay timestamp
);


-------------------------------------------------------------------------------
--
-- 存储过程
--

--
-- 测试
--
CREATE OR REPLACE FUNCTION cf_test(a integer, b integer) RETURNS integer AS $$
DECLARE
    s integer := 0;
BEGIN
    s := a + b;
    RETURN s;
END;
$$ LANGUAGE plpgsql;

--
-- 禁止IP地址判定
--
CREATE OR REPLACE FUNCTION cf_forbidip(checkip text) RETURNS integer AS $$
DECLARE
    ipstr text;
    s1 text;
    s2 text;
    s3 text;
BEGIN
    --raise info 'mydebuginfo';

    s1 = split_part(checkip, '.', 1);
    ipstr = s1 || '.*.*.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    s2 = split_part(checkip, '.', 2);
    ipstr = s1 || '.' || s2 || '.*.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    s3 = split_part(checkip, '.', 3);
    ipstr = s1 || '.' || s2 || '.' || s3 || '.*';
    PERFORM * FROM t_forbidip WHERE ip=ipstr;
    IF FOUND THEN
        RETURN 1;
    END IF;

    PERFORM * FROM t_forbidip WHERE ip=checkip;
    IF FOUND THEN
        RETURN 1;
    END IF;

    RETURN 0;
END;
$$ LANGUAGE plpgsql;

--
-- 获取用户数据
-- 如果用户不存在,则根据传入的数据创建
--
CREATE OR REPLACE FUNCTION cf_get_user_data(u_account text,
    u_name text, u_sex integer)
    RETURNS TABLE(
        result int,
        userid text,
        name text,
        sex int,
        diamond int,
        gold bigint,
        roomid int
    ) AS $$
DECLARE
    result integer := 0;
    a_type text;
    r_userid text;
    r_name text;
    r_sex integer := 0;
    r_diamond integer := 0;
    r_gold bigint := 0;
BEGIN
    -- check account is exsit?
    SELECT type INTO a_type FROM t_account WHERE account=$1;
    IF NOT FOUND THEN
        result := -1;
        RETURN QUERY SELECT result,0,'',0,0,0;
    END IF;
    SELECT userid,name,sex,diamond,gold
        INTO r_userid,r_name,r_sex,r_diamond,r_gold FROM t_user WHERE account=$1;
    IF NOT FOUND THEN
        INSERT INTO t_user(nextval('userid_sequence'),account,name,sex) VALUES($1,$2,$3);
        SELECT userid,name,sex,diamond,gold
            INTO r_userid,r_name,r_sex,r_diamond,r_gold WHERE account=$1;
    END IF;
    result := 1;
    RETURN QUERY SELECT result,r_userid,r_name,r_sex,r_diamond,r_gold;
END;
$$ LANGUAGE plpgsql;

--
-- 开房
-- 1:成功
-- <=0:失败
-- -1:uuid已存在
-- -2:roomid已存在
--
CREATE OR REPLACE FUNCTION cf_create_room(
    room_uuid text, room_id text,set_info text)
    RETURNS integer AS $$
BEGIN
    IF length(room_uuid) = 0 THEN
        RETURN 0;
    END IF;
    IF length(room_id) != 6 THEN
        RETURN 0;
    END IF;
    -- check room is exsit?
    PERFORM * FROM t_room WHERE uuid=$1;
    -- GET DIAGNOSTICS rows = ROW_COUNT;
    -- RAISE NOTICE 'row is %', rows;
    IF FOUND THEN
        RETURN -1;
    END IF;
    PERFORM * FROM t_room WHERE id=$2;
    IF FOUND THEN
        RETURN -2;
    END IF;
    
    INSERT INTO t_room(uuid,id,set_info,time_create)
        VALUES($1,$2,$3,now());
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

--
-- 加入房间
-- 0-n:成功(座位号)
-- -1:房间不存在
-- -2:满了
--
CREATE OR REPLACE FUNCTION cf_join_room(
    room_id text, user_id integer)
    RETURNS integer AS $$
DECLARE
    seat_id integer := -2;
    uid1 integer;
    uid2 integer;
    uid3 integer;
    uid4 integer;
BEGIN
    IF length(room_id) != 6 THEN
        RETURN -1;
    END IF;
    IF length(user_id) = 0 THEN
        RETURN -1;
    END IF;
    -- check room is exsit?
    PERFORM * FROM t_room WHERE id=$1;
    IF NOT FOUND THEN
        RETURN -1;
    END IF;
    
    SELECT user_id1,user_id2,user_id3,user_id4 INTO
        uid1,uid2,uid3,uid4
        FROM t_room WHERE id=$1;
    -- if have join histroy
    IF uid1 == user_id THEN
        UPDATE t_room SET user_id1=$2 WHERE id=$1;
        RETURN 0;
    END IF;
    IF uid2 == user_id THEN
        UPDATE t_room SET user_id2=$2 WHERE id=$1;
        RETURN 1;
    END IF;
    IF uid3 == user_id THEN
        seat_id := 2;
    END IF;
    IF seat_id != -2 AND uid3 == user_id THEN
        seat_id := 3;
    END IF;
    -- now get a empty seat
    IF seat_id != -2 AND uid1 == 0 THEN
        seat_id := 0;
    END IF;
    IF seat_id != -2 AND uid2 == 0 THEN
        seat_id := 1;
    END IF;
    IF seat_id != -2 AND uid3 == 0 THEN
        seat_id := 2;
    END IF;
    IF seat_id != -2 AND uid3 == 0 THEN
        seat_id := 3;
    END IF;
    IF seat_id == -2 THEN
        RETURN -2;
    END IF;
    RETURN seat_id;
END;
$$ LANGUAGE plpgsql;
